const connection = require("../app/database");

class ListService {
  // 根据标签返回数据列表
  async getListByTag(tag) {
    try {
      const statement = `SELECT
        l.id AS id,
        l.location AS location,
        l.desc AS description,
        l.open_time AS openTime,
        l.price AS price,
        ROUND(l.score, 2) AS score,
        JSON_ARRAYAGG(i.image_url) AS imgUrl
        FROM list l
        LEFT JOIN image AS i ON i.list_item_id = l.id
        WHERE l.tag_id = ? AND l.state = 1
        GROUP BY l.id;`;
      const [result, fileds] = await connection.execute(statement, [tag]);
      return result;
    } catch (err) {
      console.log(err);
    }
  }
  // 根据输入内容查询数据
  async findListByContent(content) {
    try {
      const statement = `SELECT
                          l.id AS id,
                          l.location AS location,
                          l.desc AS description,
                          l.open_time AS openTime,
                          l.price AS price,
                          ROUND(l.score, 2) AS score,
                          JSON_ARRAYAGG(i.image_url) AS imgUrl
                          FROM list l
                          LEFT JOIN image AS i ON i.list_item_id = l.id
                          WHERE l.location LIKE ? OR l.desc LIKE ? AND l.state = 1
                          GROUP BY l.id;`;
      const [res, fileds] = await connection.execute(statement, [`%${content}%`, `%${content}%`])
      return res
    } catch (err) {
      console.log(err);
    }
  }
  // 根据id返回数据
  async getListById(id) {
    try {
      const statement = `SELECT
                          l.id AS id,
                          l.location AS location,
                          l.desc AS description,
                          l.open_time AS openTime,
                          l.price AS price,
                          ROUND(l.score, 2) AS score,
                          JSON_ARRAYAGG(i.image_url) AS imgUrl
                          FROM list l
                          LEFT JOIN image AS i ON i.list_item_id = l.id
                          WHERE l.id = ?
                          GROUP BY l.id;`
      const [res, fileds] = await connection.execute(statement, [id])
      return res[0]
    } catch(err) {
      console.log(err)
    }
  }
  // 修改数据的状态
  async updateListState(id) {
    const statement = `UPDATE list SET list.state = 0 WHERE id = ?;`
    try {
      await connection.execute(statement, [id])
    } catch(err) {
      console.log(err)
    }
  }
  // 恢复数据的状态
  async restoreListState(id) {
    const statement = `UPDATE list SET list.state = 1 WHERE id = ?;`
    try {
      await connection.execute(statement, [id])
    } catch(err) {
      console.log(err)
    }
  }
}

module.exports = new ListService();
